CREATE proc [dbo].[TeamMembersDayOff] @ProjectNodeGUID nvarchar(255),@IterationSK int,@Domain nvarchar(255)
as begin
SELECT distinct con.DisplayPart, 
((SELECT(DATEDIFF(dd, [StartTime], [EndTime]) + 1)-(DATEDIFF(wk, [StartTime], [EndTime]) * 2)-(CASE WHEN DATENAME(dw, [StartTime]) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, [EndTime]) = 'Saturday' THEN 1 ELSE 0 END)) )as 'No Of Days Off/Leave'
,iter.IterationPath, team.TeamFieldValue,
	  convert(date,StartDate) as 'IterationStart (YYYY-MM-DD)',convert(date,FinishDate) as 'IterationEnd (YYYY-MM-DD)',
	  convert(date,[StartTime]) as 'Leave From (YYYY-MM-DD)',convert(date,[EndTime]) as 'Leave To (YYYY-MM-DD)'
FROM [Tfs_FabrikamFiberCollection].[dbo].[tbl_TeamConfigurationCapacityDaysOffRange] Tc inner join
[Tfs_FabrikamFiberCollection].[dbo].[tbl_TeamConfigurationTeamFields] team on TC.TeamId=team.TeamId inner join 
  [Tfs_FabrikamFiberCollection].[dbo].[Constants] Con on TC.TeamMemberId=con.TeamFoundationId inner join
[Tfs_Warehouse].[dbo].[DimIteration] iter on TC.IterationId=iter.IterationGUID 
 where  iter.ProjectGUID=@ProjectNodeGUID and iter.IterationSK = @IterationSK and Con.DomainPart=@Domain
  order by DisplayPart
 end



